PGCM 模块一安全机制实验

#PGCM 模块一安全机制实验

源码编译安装PostgreSQL 10版本，要求如下：

使用操作系统用户pg15进行编译安装，软件安装目录为/home/pg15/soft,编译安装时需要开启ssl的支持
$PGDATA目录是/home/pg15/data,PostgreSQL数据库的超级用户是postgres，postgres用户的密码设置为 1qaz@WSX ，并体现到.pgpass文件中，以便postgres免密登录，初始化时开启checksum，监听端口号请设置为5666，然后启动数据库。
为数据库配置SSL支持，并建立sslinfo扩展。
配置密码复杂度检查模块

执行 [[optimize_system_conf_pgcm.sh]]或者手动准备操作系统环境。

1 创建安装目录

su - pg15
mkdir -p /home/pg15/soft

2 创建源码目录

root>
mkdir -p /soft
chown pg15:pg15 /soft

3 配置用户环境变量

su - pg15
cat >> ~/.bash_profile << EOF
export PGPORT=5666
export PGHOME=/home/pg15/soft
export PATH=\$PGHOME/bin:\$PATH
export PGDATA=/home/pg15/data
export LD_LIBRARY_PATH=\$PGHOME/lib
export LANG=en_US.UTF-8
EOF
source ~/.bash_profile

4 安装软件依赖包

root>
yum install gcc gcc-c++ zlib-devel readline-devel  psmisc perl-ExtUtils-Embed pam-devel libxml2-devel libxslt-devel openldap-devel python39-devel python2-devel openssl-devel cmake  tcl tcl-devel tk-devel libicu-devel bash-completion systemd-devel bison flex docbook-dtds docbook-style-xsl  libxslt   lz4-devel  libzstd-devel wget git vim -y

5 源码下载

su - pg15
cd /soft
wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz

6 编译postgresql 软件包并安装到指定目录

su - pg15
cd /soft/
tar -zxf postgresql-*.tar.gz
cd /soft/postgresql-*/
./configure --prefix=/home/pg15/soft  --with-systemd  --with-ssl=openssl  --enable-debug --with-icu  --enable-nls --with-zlib --with-libxml --with-perl --with-ldap --with-python --with-libxslt  --with-uuid=e2fs --with-pam --with-gssapi --with-zstd --with-lz4 --with-tcl
make -j 8
make install

7 编译第三方插件并安装

Warning

要使用root 用户编译和安装。

#包括第三方插件全部编译
cd contrib 

#这个需要使用普通用户执行，可选，耗时较长
make  -j 8
#包括第三方插件全部安装
make install

8 创建数据库集簇

8.1 创建目录

pg15>
mkdir -p /home/pg15/data

8.2 初始化数据库集簇

postgres>
initdb -D $PGDATA -U postgres -W --data-checksums  -A scram-sha-256  -E UTF-8

password:1qaz@WSX

Warning

1、要想初始化集簇为英文，需要设置 LANG=en_US.UTF-8。
2、更多关于Postgersql 能够初始化的字符集请见 PostgreSQL: Documentation: 15: 24.3. Character Set Support
3、复制时需要 data-cecksums 支持块校验。

8.3 配置数据库参数

echo "port=5666" >> $PGDATA/postgresql.conf
echo "listen_addresses = '*'" >> $PGDATA/postgresql.conf

8.4 配置pg_hba.conf

vi $PGDATA/pg_hba.conf 
host    all             all             192.168.10.0/24         scram-sha-256

8.5 配置.pgpass

hostname:port:database:username:password

vi ~/.pgpass 
*:5666:*:postgres:1qaz@WSX

chmod 600 ~/.pgpass

8.6 启动数据库集簇

postgres>
pg_ctl start -D $PGDATA
psql -U postgres -d postgres

9 配置SSL 支持，创建sslinfo 扩展

PostgerSQL 安全管理自签名私有证书

psql -U postgres -d postgres
CREATE EXTENSION sslinfo;
select ssl_cipher();


select ssl_version() ;

10 配置密码复杂度检查模块

PostgreSQL 安全管理 passworkcheck

基础环境配置，要求如下：

启动数据库服务，使用超级用户postgres创建应用用户appuser，赋权createdb、login，appuser的密码设置为 1qaz@WSX ，并体现到.pgpass文件中，以便appuser免密登录，appuser用户的密码在2023年9月30日之前是有效的。

psql -U postgres -d postgres 
CREATE USER appuser VALID UNTIL '2023-09-30' CREATEDB LOGIN;
\du appuser
\password appuser 
password 1qaz@WSX
echo "*:5666:*:appuser:1qaz@WSX" >> ~/.pgpass 
chmod 600 ~/.pgpass 
cat ~/.pgpass
psql -U appuser -d postgres

创建属主为appuser的表空间并命名为exam，指向/exam（若没有此目录请自行创建并管理权限）；

su - root  
mkdir /exam 
chown pg15:pg15 /exam 
su - pg15
psql -U postgres -d postgres 
CREATE TABLESPACE exam location '/exam';
ALTER TABLESPACE exam OWNER TO appuser ;
\db+

创建appdb数据库，owner是appuser（要求appdb数据库要在exam表空间内），并要求实现：
(1) 回收appdb中的public schema上的create object权限。
(2) 以appuser用户在appdb数据库中创建名为appuser的schema
(3) 以appuser用户在appdb数据库中创建app表（id int），app表的schema是appuser
(4) 其他的非超级用户不能connect到appdb数据库中

psql -U appuser -d postgres 
CREATE DATABASE appdb TABLESPACE exam ;
\l+  appdb
psql -U appuser -d appdb 
REVOKE CREATE ON SCHEMA public FROM public ;
\dn+ 

CREATE SCHEMA appuser;
CREATE TABLE appuser.app(id int);
REVOKE CONNECT ON DATABASE appdb FROM public ;
\l+ appdb

自行建立readonlyuser用户，要求如下：
(1) readonlyuser能连接到appdb中
(2) 密码设置为1qaz@WSX，并体现到.pgpass文件中，以便readonlyuser免密登录
(3) readonlyuser用户有对appuser用户下appuser schema下所有表（包括将来新建立的表）的只读权限；

psql -U postgres -d appdb
CREATE USER readonlyuser PASSWORD '1qaz@WSX';

echo "*:5666:*:readonlyuser:1qaz@WSX" >> ~/.pgpass

psql -U appuser -d appdb
GRANT CONNECT ON DATABASE  appdb to readonlyuser;
GRANT USAGE ON SCHEMA appuser  TO readonlyuser;
GRANT SELECT ON ALL TABLES IN SCHEMA appuser TO readonlyuser;
ALTER DEFAULT PRIVILEGES FOR USER appuser 
IN SCHEMA appuser
GRANT SELECT ON TABLES TO readonlyuser;

psql -U readonlyuser -d appdb
table appuser.app;

修改集群（实例）的配置，要求如下：
(1) 运行日志路径/pglog
(2) 运行日志格式修改为'csvlog'
(3) 每天生成一个新的日志文件
(4) 不限制单个日志文件的大小
(5) PostgreSQL的运行log日志需要保留最近1个月的log日志文件。
(6) 最多允许100个非超级用户的连接连入数据库中。
(7) 运行日志内容中要体现时间戳(带毫秒，不适用unix时间戳)，用户名，数据库名称和进程ID，依照上述顺序
(8) 需要监听的地址设置为*，
(9) 开启全页写配置
(10) 数据库用户的密码加密算法修改为scram-sha-256，并确保数据库用户能正常登录。
(11) 配置好.pgpass文件，以便postgres用户可以免密输入

su - root 
mkdir -p /pglog 
chown pg15:pg15 /pglog

psqlgres> cat >> $PGDATA/err_log.conf << EOF
log_directory = '/pglog'
log_destination = 'csvlog'
logging_collector = on
log_filename = 'postgresql-%Y-%m-%d'
log_line_prefix ='%m %u %d [%p]'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
log_error_verbosity = verbose
EOF

cat >> $PGDATA/postgresql.conf << EOF
include_if_exists='err_log.conf'
EOF

grep "listen_addresses = '*'" $PGDATA/postgresql.conf
echo "wal_log_hints = on" >> $PGDATA/postgresql.conf
echo "full_page_writes = on" >> $PGDATA/postgresql.conf
echo "max_connections=103" >>  $PGDATA/postgresql.conf

cat ~/.pgpass 
cat $PGDATA/pg_hba.conf

pg_hba .conf配置要求

1) 本地访问需要输入postgres用户的密码，密码加密方式为scram-sha-256；
2) 在禁止使用pg_hba.conf中hostssl这个connection type的前提条件下，请完成如下目标：允许任意客户端或应用服务器的IP地址或网段访问，认证方式为scram-sha-256。

3) 禁止postgres超级用户通过网络TCP/IP访问集群（实例）服务；

# "local" is for Unix domain socket connections only
local   all             postgres                                scram-sha-256
# IPv4 local connections:
host    all             postgres        0.0.0.0/0               reject
host    all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

psql -h 127.0.0.1 -U postgres -d postgres

psql -h 127.0.0.1 -U appuser -d appdb

行级安全性RLS ：

1) 以postgres用户登录postgres数据库，
执行RLSData.sql, 并确保在passwd表上启用行级安全性

psql -U postgres -d postgres
\i /soft/RLSData.sql

2) 创建策略，以实现如下要求：
(1) 策略名称为appuser1_select_passwd，appuser1用户仅仅可以select出来 user_name=‘userapp1’的记录。

psql -U postgres -d postgres
DROP POLICY appuser1_select_passwd ON passwd;
CREATE POLICY appuser1_select_passwd ON passwd TO appuser1 USING (user_name = 'appuser1');
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
psql -h 127.0.0.1 -U appuser1 -d postgres
SELECT *  FROM passwd ;
//屏幕输出：
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |    home_dir    |  shell
-----------+--------+-----+-----+-----------+--------------+------------+----------------+----------
 appuser1  | xxx    |   1 |   1 | appuser1  | 123-456-7890 |            | /home/appuser1 | /bin/zsh
(1 row)

(2) 策略名称为appuser1_insert_passwd，允许以appuser1用户能成功执行如下插入SQL:

psql -U postgres -d postgres
DROP POLICY appuser1_insert_passwd ON passwd;
create policy appuser1_insert_passwd on passwd for insert to appuser1 with check(true);
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
psql -h 127.0.0.1 -U appuser1 -d postgres
Insert into passwd
values('readonlyuser','yyy',3,3,'readonlyuser','333-444-555',null,'/home/readonlyuser','/bin/zsh');

敏感数据加密：使用pgcrypto模块给敏感数据加密，要求如下：

以postgres用户登录postgres在public模式下建立test_user表，该表有如下几列：
id，序列类型
user_name，varchar(32)类型
password，text类型

psql -U postgres -d postgres 
CREATE TABLE test_user (id serial ,username varchar(32), password text);

插入数据，要求如下：
在test_user表中插入两行，user_name列分别为appuser1和appuser2，password列为123456，并使用crypt()函数加密该密码,gen_salt请使用md5算法。

[ pg15@node1 pglog]$ psql  -U postgres -d postgres
psql (15.4)
Type "help" for help.

postgres=# CREATE EXTENSION pgcrypto ;
CREATE EXTENSION

insert into test_user(username,password) values('appuser1',crypt('123456',gen_salt('md5')));
insert into test_user(username,password) values('appuser2',crypt('123456',gen_salt('md5')));
select * from test_user;
//屏幕输出：
 id | username |              password
----+----------+------------------------------------
  1 | appuser1 | $1$kaXzcWGt$M1GAT3YEasR/Mw2Em87O01
  2 | appuser2 | $1$Ed5/82x5$p.zYOo3/J6a./XzJTCdZA1
(2 rows)

启用pgaudit插件，要求如下：

1) 创建审计用户 appauditor

2) 实现:对 appdb数据库下的appuser.app表的select和delete操作进行审计。

psql -U postgres -d appdb
DROP USER appauditor;
CREATE USER appauditor PASSWORD '1qaz@WSX';

安装 pgaudit 插件

psql -U postgres -d appdb
CREATE EXTENSION pgaudit;
SET pgaudit.role = 'appauditor';
GRANT CONNECT ON DATABASE appdb TO appauditor;
GRANT ALL ON SCHEMA appuser TO appauditor ;
GRANT SELECT ON appuser.app TO appauditor; 
GRANT DELETE ON appuser.app TO appauditor;

\c - appauditor
SELECT * FROM appuser.app;
DELETE * FROM appuser.app;